<?php 
header("content-type:text/html;charset=utf8");
$pdo = new PDO("mysql:host=123.57.16.97;dbname=device_binding","root","zqVnUy6cxsQXbpEZ");
$pdo->exec("set names utf8");

$p = isset($_GET['p'])?$_GET['p']:1;
$size = 15;
if ($p == 1) {
    $limit = 0;
}else{
    $limit = ($p-1)*$size;
}

if (isset($_GET['tel']) && !empty($_GET['tel'])) {
    $tel = $_GET['tel'];
}

if (isset($_GET['openid']) && !empty($_GET['openid'])) {
    $openid = $_GET['openid'];
}

if (isset($_GET['device_sn']) && !empty($_GET['device_sn'])) {
    $device_sn = $_GET['device_sn'];
}

//判断传输条件开始进行查询
if (isset($device_sn)) {

    $sql = "SELECT * FROM statistics WHERE device_sn like '%$device_sn%'";
    $device_info = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    if ($device_info) {
        $u_id = '';
        foreach ($device_info as $key => $value) {
            $u_id .= $value['u_id'].',';
        }
        $u_id = trim($u_id,',');
        $sql = "SELECT * FROM user WHERE u_id in($u_id)";
        if (isset($tel)) {
            $sql .= " AND tel like '%$tel%'";
        }elseif(isset($openid)){
            $sql .= " AND openid like '%$openid%'";
        }
        $count = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        $pcount = floor(count($count)/$size);
        $sql .= " limit $limit,$size";
        $user_info = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }

}else if(isset($tel) && isset($openid)){

    $sql = "SELECT * FROM user WHERE tel like '%$tel%' AND openid like '%$openid%'";
    $count = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    $pcount = floor(count($count)/$size);
    $sql .= " limit $limit,$size";
    $user_info = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);

}else if(isset($tel) && !isset($openid)){

    $sql = "SELECT * FROM user WHERE tel like '%$tel%'";
    $count = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    $pcount = floor(count($count)/$size);
    $sql .= " limit $limit,$size";
    $user_info = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);

}else if(!isset($tel) && isset($openid)){

    $sql = "SELECT * FROM user WHERE openid like '%$openid%'";
    $count = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    $pcount = floor(count($count)/$size);
    $sql .= " limit $limit,$size";
    $user_info = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);

}else{

    $sql = "SELECT * FROM user";
    $count = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    $pcount = floor(count($count)/$size);
    $sql .= " limit $limit,$size";
    $user_info = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}
foreach ($user_info as $key => &$value) {
    $sql = "SELECT * FROM statistics WHERE u_id = '".$value['u_id']."'";
    $device_info = $pdo->query($sql)->fetch(PDO::FETCH_ASSOC);
    $value['device_sn'] = $device_info['device_sn'];
    // $value['nickname'] = getWeixinName($value['openid']);
    $value['nickname'] = '';
}

$user_info = changeRed($user_info,isset($tel)?$tel:'',isset($openid)?$openid:'',isset($device_sn)?$device_sn:'');

function changeRed($user_info,$tel,$openid,$device_sn){
    foreach ($user_info as $key => &$value) {
        $value['tel'] = str_replace($tel, "<span style='color:red;'>$tel</span>", $value['tel']);
        $value['openid'] = str_replace($openid, "<span style='color:red;'>$openid</span>", $value['openid']);
        $value['device_sn'] = str_replace($device_sn, "<span style='color:red;'>$device_sn</span>", $value['device_sn']);
    }
    return $user_info;
}

function getWeixinName($openid){
    if (isset($_COOKIE['access_token'])) {
        $access_token = $_COOKIE['access_token'];
    }else{
        $getAccessTokenUrl = "https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid=wx2de38da107527240&secret=446fdd6335e31f0ceeaf63562ff03a4c";
        $access_token_array = json_decode(file_get_contents($getAccessTokenUrl),true); 
        setcookie('access_token',$access_token_array['access_token'],time()+7200);
        $access_token = $access_token_array['access_token'];    
    } 
    $getWeixinInfoUrl = "https://api.weixin.qq.com/cgi-bin/user/info?access_token=".$access_token."&openid=".$openid."&lang=zh_CN";
    $weixinInfo = json_decode(file_get_contents($getWeixinInfoUrl),true);
    if ($weixinInfo['nickname']) {
        return $weixinInfo['nickname'];
    }else{
        return '';
    }
}
 ?> 
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>用户信息统计</title>

<link href="css/bootstrap.min.css" rel="stylesheet">
<link href="css/datepicker3.css" rel="stylesheet">
<link href="css/styles.css" rel="stylesheet">

</head>

<body>
	<nav class="navbar navbar-inverse navbar-fixed-top" role="navigation">
		<div class="container-fluid">
			<div class="navbar-header">
				<button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#sidebar-collapse">
					<span class="sr-only">Toggle navigation</span>
					<span class="icon-bar"></span>
					<span class="icon-bar"></span>
					<span class="icon-bar"></span>
				</button>
				<a class="navbar-brand" href=""><span>数据统计</span>嗨镜</a>
				<ul class="user-menu">
					<li class="dropdown pull-right">
						<a href="#" class="dropdown-toggle" data-toggle="dropdown"><span class="glyphicon glyphicon-user"></span> User <span class="caret"></span></a>
						<ul class="dropdown-menu" role="menu">
							<li><a href="#"><span class="glyphicon glyphicon-log-out"></span> 退出登陆</a></li>
						</ul>
					</li>
				</ul>
			</div>
		</div><!-- /.container-fluid -->
	</nav>
		
	<div id="sidebar-collapse" class="col-sm-3 col-lg-2 sidebar">
		<ul class="nav menu">
            <li><a href="index.php"><span class="glyphicon glyphicon-dashboard"></span> 设备日活统计</a></li>
			<li><a href="application_index.php"><span class="glyphicon glyphicon-dashboard"></span> 应用日活统计</a></li>
            <li><a href="device_use_duration.php"><span class="glyphicon glyphicon-th"></span> 设备使用时长统计</a></li>
            <li><a href="application_use_duration.php"><span class="glyphicon glyphicon-th"></span> 应用使用时长统计</a></li>
			<li><a href="device_info.php"><span class="glyphicon glyphicon-stats"></span> 设备信息统计</a></li>
			<li  class="active"><a href="user_info.php"><span class="glyphicon glyphicon-list-alt"></span> 用户信息统计</a></li>
			<li><a href="application_info.php"><span class="glyphicon glyphicon-pencil"></span> 应用信息统计</a></li>
            <li><a href="device_time_interval.php"><span class="glyphicon glyphicon-info-sign"></span> 设备日均时段统计</a></li>
			<li><a href="application_time_interval.php"><span class="glyphicon glyphicon-info-sign"></span> 应用日均时段统计</a></li>
		</ul>
		<div class="attribution">More Templates <a href="http://www.cssmoban.com/" target="_blank" title="模板之家">模板之家</a> - Collect from <a href="http://www.cssmoban.com/" title="网页模板" target="_blank">网页模板</a></div>
	</div><!--/.sidebar-->
		
	<div class="col-sm-9 col-sm-offset-3 col-lg-10 col-lg-offset-2 main">
        <form action="./user_info.php">
            <div class="form-group" border="1">
                <b>手机号：</b><input name="tel" id="tel" value="<?php echo isset($tel)?$tel:'' ?>" type="text" style="width:120px;margin-top:30px;border:2;border-color:green;" placeholder="手机号">
                <b>微信号：</b><input name="openid" id="openid" value="<?php echo isset($openid)?$openid:'' ?>" type="text" style="width:120px;margin-top:30px;border:2;border-color:green;" placeholder="微信号">
                <b>设备码：</b><input name="device_sn" id="device_sn" value="<?php echo isset($device_sn)?$device_sn:'' ?>" type="text" style="width:120px;margin-top:30px;border:2;border-color:green;" placeholder="设备码">
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input type="submit" style="width:120px;border:2;border-color:yellow;background-color:yellow;" value="搜索">
            </div>
        </form>			
        <br>
		<table border="1" style="width: 100%;height: 80%">
            <tr style="height: 30px">
                <td align="center">用户ID</td>
                <td align="center">绑定手机</td>
                <td align="center">注册时间</td>
                <td align="center">微信号</td>
                <td align="center">微信昵称</td>
                <td align="center">绑定设备</td>
            </tr>
            <?php foreach ($user_info as $key => $value) { ?>
            <tr style="height: 30px">
                <td align="center"><?php echo $value['u_id'] ?></td>
                <td align="center"><?php echo $value['tel'] ?></td>
                <td align="center"><?php echo $value['time'] ?></td>
                <td align="center"><?php echo $value['openid'] ?></td>
                <td align="center"><?php echo $value['nickname'] ?></td>
                <td align="center"><?php echo $value['device_sn'] ?></td>
            </tr>
            <?php } ?>
        </table>
        <br>
        <center>
        <b>当前页码数 <span id="p" style="color: red"><?php echo $p; ?></span></b>
        <button id="first">首页</button>
        <button id="upper">上一页</button>
        <button id="lower">下一页</button>
        <button id="tail">尾页</button>
        <b>当前总页数 <span id="pcount" style="color:red"><?php echo $pcount ?></span></b>
        </center>
	</div>	<!--/.main-->

	<script src="js/jquery-1.11.1.min.js"></script>
	<script src="js/bootstrap.min.js"></script>
	<script src="js/chart.min.js"></script>
	<script src="js/chart-data.js"></script>
	<script src="js/easypiechart.js"></script>
	<script src="js/easypiechart-data.js"></script>
	<script type="text/javascript" src="./js/echarts.min.js"></script>
	<!-- <script src="js/bootstrap-datepicker.js"></script> -->
</body>

</html>

<script>
    $("#first").click(function() {
        var tel = $("#tel").val();
        var openid = $("#openid").val();
        var device_sn = $("#device_sn").val();
        window.location.href='./user_info.php?tel='+tel+'&openid='+openid+'&device_sn='+device_sn;
    })

     $("#upper").click(function() {
        var p = parseInt($("#p").html())-parseInt(1);
        if (p < "1") {
            alert("当前属于第一页");
            return false;
        }
        var tel = $("#tel").val();
        var openid = $("#openid").val();
        var device_sn = $("#device_sn").val();
        window.location.href='./user_info.php?tel='+tel+'&openid='+openid+'&device_sn='+device_sn+'&p='+p;
    })

    $("#lower").click(function() {
        var p = parseInt($("#p").html())+parseInt(1);
        var pcount = parseInt($("#pcount").html());
        if (p > pcount) {
            alert("当前属于尾页");
            return false;
        }
        var tel = $("#tel").val();
        var openid = $("#openid").val();
        var device_sn = $("#device_sn").val();
        window.location.href='./user_info.php?tel='+tel+'&openid='+openid+'&device_sn='+device_sn+'&p='+p;
    })

     $("#tail").click(function() {
        var p = $("#pcount").html();
        var tel = $("#tel").val();
        var openid = $("#openid").val();
        var device_sn = $("#device_sn").val();
        window.location.href='./user_info.php?tel='+tel+'&openid='+openid+'&device_sn='+device_sn+'&p='+p;
    })
</script>
